package com.easa.dao;

import com.easa.entity.ShowTimetableByDay;
import com.easa.entity.Timetable;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

@Repository
@Transactional
public class TimetableDaoImpl implements TimetableDao{

    @Autowired
    private JdbcTemplate jdbcTemplate;

    /*找当日课程*/
    public List<ShowTimetableByDay> findCourseWithToday(Date today,String weekid){

        StringBuilder sql = new StringBuilder("SELECT tt.cid,tt.lessonName,tm.tname,tt.weekid,tt.classNum," +
                "tt.classLength,tt.classDate,lm.lname,tt.applicationTime, tt.applicationName,tt.lid, tt.weekday, tt.batchNum from timetable tt, teachermsg tm, laboratorymsg lm " +
                "where tt.classDate = ? and tt.weekid= ? and tt.tid = tm.tid and tt.lid = lm.lid and lm.lstatus = 0");
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        String dateStr = sdf.format(today);
        List<ShowTimetableByDay> list;
        try {
            list =  jdbcTemplate.query(String.valueOf(sql), new Object[]{dateStr,weekid}, new BeanPropertyRowMapper(ShowTimetableByDay.class));
        }catch (Exception e){
            System.out.println(e);
            list = null;
        }
        return list;
    }

    /*查找timetable*/
    public List<Timetable> findAllTimetable(Date today){

        String sql="SELECT * FROM timetable";
        List<Timetable> list;
        try {
            list =  jdbcTemplate.query(sql, new BeanPropertyRowMapper(ShowTimetableByDay.class));
        }catch (Exception e){
            System.out.println(e);
            list = null;
        }
        return list;
    }

    //插入课程
    public int insertClass(Timetable timetable){

        if(findIsExistClass(timetable))
            return 2;

        String sql = "INSERT INTO timetable(cid, lessonName, tid, weekid, classNum," +
                "classLength,classDate,lid,applicationName,applicationTime," +
                "classClassify,weekday,batchNum) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?)";
        Date date = new Date();// 获取当前时间
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
        Date classdate = null;
        try {
            classdate = format.parse(timetable.getClassDate());
            System.out.print(date);
        } catch (ParseException e) {
            e.printStackTrace();
        }
        long maxId = getMaxId();
        try {
            jdbcTemplate.update(sql, new Object[]{maxId+1, timetable.getLessonName(),
                    timetable.getTid(), timetable.getWeekid(), timetable.getClassNum(),
                    timetable.getClassLength(), classdate,timetable.getLid(),
                    timetable.getApplicationName(),date,
                    timetable.getClassClassify(),timetable.getWeekday(),
                    timetable.getBatchNum()});
        }catch (Exception e){
            System.out.println(e);
            return 1;
        }
        return 0;
    }

    //查询是否有冲突
    private boolean findIsExistClass(Timetable timetable) {
        for (int i = 0; i < timetable.getClassLength(); i++){
            String sql="SELECT * FROM timetable WHERE classDate = ? AND lid = ? AND (? BETWEEN classNum AND classNum+classLength-1)";
            Timetable timetableTemp;
            try {
                timetableTemp = jdbcTemplate.queryForObject(sql, new Object[]{timetable.getClassDate(),timetable.getLid(),timetable.getClassNum()+i},new BeanPropertyRowMapper<Timetable>(Timetable.class));
                if (timetableTemp!=null)
                    return true;
            }catch (Exception e){
                System.out.println(e);
            }
        }
        return false;
    }

    //获取最大id
    private long getMaxId(){
        String sql = "SELECT cid FROM timetable WHERE cid = (SELECT max(cid) FROM timetable)";
        Integer maxId;
        try {
            maxId = jdbcTemplate.queryForObject(sql, java.lang.Integer.class);
        }catch (Exception e){
            System.out.println(e);
            maxId = 0;
        }
        return (long) maxId;
    }

    //根据时间获取全部课表
    public List<ShowTimetableByDay> findAllCourse(String searchDate,String weekid){

        StringBuilder sql = new StringBuilder("SELECT tt.cid,tt.lessonName,tm.tname,tt.weekid,tt.classNum," +
                "tt.classLength,tt.classDate,lm.lname,tt.applicationTime,tt.applicationName, tt.lid, tt.weekday, tt.batchNum from timetable tt, teachermsg tm, laboratorymsg lm " +
                "where tt.classDate = ? and tt.weekid= ? and tt.tid = tm.tid and tt.lid = lm.lid and lm.lstatus = 0");

        List<ShowTimetableByDay> list;
        try {
            list =  jdbcTemplate.query(String.valueOf(sql), new Object[]{searchDate,weekid}, new BeanPropertyRowMapper(ShowTimetableByDay.class));
        }catch (Exception e){
            System.out.println(e);
            list = null;
        }
        return list;
    }

    //根据时间获取个人课表
    public List<ShowTimetableByDay> findPersonCourse(String searchDate, String tid){
        StringBuilder sql = new StringBuilder("SELECT tt.cid,tt.lessonName,tm.tname,tt.weekid,tt.classNum," +
                "tt.classLength,tt.classDate,lm.lname,tt.applicationTime,tt.applicationName,tt.lid,tt.weekday, tt.batchNum from timetable tt, teachermsg tm, laboratorymsg lm " +
                "where tt.classDate = ? and tt.tid = tm.tid and tt.lid = lm.lid and tt.tid = ? and lm.lstatus = 0");

        List<ShowTimetableByDay> list;
        try {
            list =  jdbcTemplate.query(String.valueOf(sql), new Object[]{searchDate,tid}, new BeanPropertyRowMapper(ShowTimetableByDay.class));
        }catch (Exception e){
            System.out.println(e);
            list = null;
        }
        return list;
    }

    //根据周获取个人课表
    public List<ShowTimetableByDay> findPersonCourseAWeek(String weekid, String tid){
        StringBuilder sql = new StringBuilder("SELECT tt.cid,tt.lessonName,tm.tname,tt.weekid,tt.classNum," +
                "tt.classLength,tt.classDate,lm.lname,tt.applicationTime,tt.applicationName,tt.lid,tt.weekday, tt.batchNum from timetable tt, teachermsg tm, laboratorymsg lm " +
                "where tt.weekid = ? and tt.tid = tm.tid and tt.lid = lm.lid and tt.tid = ?");

        List<ShowTimetableByDay> list;
        try {
            list =  jdbcTemplate.query(String.valueOf(sql), new Object[]{weekid,tid}, new BeanPropertyRowMapper(ShowTimetableByDay.class));
        }catch (Exception e){
            System.out.println(e);
            list = null;
        }
        return list;
    }

    //获取详细的个人课程信息
    public ShowTimetableByDay findTimetableByCid(int cid){
        StringBuilder sql = new StringBuilder("SELECT tt.cid,tt.lessonName,tm.tname,tt.weekid,tt.classNum," +
                "tt.classLength,tt.classDate,lm.lname,tt.applicationTime,tt.applicationName,tt.lid,tt.weekday, tt.batchNum,tt.classClassify,lm.lstatus from timetable tt, teachermsg tm, laboratorymsg lm " +
                "where tt.tid = tm.tid and tt.lid = lm.lid and tt.cid = ?");

        ShowTimetableByDay showTimetableByDay;
        try {
            showTimetableByDay = jdbcTemplate.queryForObject(String.valueOf(sql), new Object[]{cid}, new BeanPropertyRowMapper<ShowTimetableByDay>(ShowTimetableByDay.class));
        }catch (Exception e){
            System.out.println(e);
            showTimetableByDay = null;
        }
        return showTimetableByDay;
    }

    //删除课程
    public int deleteClassByCid(int cid){

        ShowTimetableByDay timetable = findTimetableByCid(cid);
        Date date = new Date();
        SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
        Date classDate = null;
        try {
            classDate=sdf.parse(timetable.getClassDate());
        } catch (ParseException e) {
            e.printStackTrace();
        }
        System.out.println("date.before(classDate)="+date.before(classDate));
        if (classDate==null || !date.before(classDate)){
            //取消以前的课程的操作不允许 提前一个工作日取消课程
            return 1;
        }

        String sql = "DELETE FROM timetable WHERE cid = ? ";

        try {
            jdbcTemplate.update(sql, new Object[]{cid});
            return 0;
        }catch (Exception e){
            System.out.println(e);
            return 2;//取消出错
        }

    }

    //(后台管理 多条件查询可课程)
    public List<ShowTimetableByDay> getClassByAdmin(String tid, String lname, String classdate){


        StringBuilder sql = new StringBuilder("SELECT tt.cid,tt.lessonName,tm.tname,tt.weekid,tt.classNum," +
                "tt.classLength,tt.classDate,lm.lname,tt.applicationTime,tt.applicationName,tt.lid,tt.weekday, tt.batchNum from timetable tt, teachermsg tm, laboratorymsg lm " +
                "where tt.tid = tm.tid and tt.lid = lm.lid ");
        List<ShowTimetableByDay> list;
        if(tid != null && !tid.equals("")){
            sql.append("and tt.tid like '%"+ tid+"%' ");
        }

        if(lname != null && !lname.equals("")){
            sql.append("and lm.lname like '%"+lname+"%' ");
        }

        if(classdate !=null && !classdate.equals("")){
            sql.append("and tt.classDate = '"+classdate+"'");
        }

        try {
            list =  jdbcTemplate.query(String.valueOf(sql), new Object[]{}, new BeanPropertyRowMapper(ShowTimetableByDay.class));
        }catch (Exception e){
            System.out.println(e);
            list = null;
        }

        return list;
    }

}
